﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace testExcel
{
    class ExcelProcess
    {
        
        //public static void DT2Excel(System.Data.DataTable dt,string filePath)
        //{
        //    //Tạo mới một file Excel
        //    COMExcel.Application exApp = new COMExcel.Application();
        //    COMExcel.Workbook exWB = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
        //    COMExcel.Worksheet exSheet=(COMExcel.Worksheet)exWB.Worksheets[1];

        //    exSheet.Activate();
        //    exSheet.Name = "Sheet";
        //    exApp.Visible = false;

        //    COMExcel.Range r;
        //    string colName;

        //    for (int i = 1; i <= dt.Columns.Count; i++)
        //    {
        //        r = (COMExcel.Range)exSheet.Cells[1, i];
        //        colName = dt.Columns[i - 1].ColumnName;
        //        r.Value2 = colName;

        //        for(int j=2;j<=dt.Rows.Count+1;j++)
        //        {
        //            r=(COMExcel.Range)exSheet.Cells[j, i];
        //            r.Value2 = dt.Rows[j-2][colName];
        //        }
        //    }

        //    exWB.SaveAs(filePath, COMExcel.XlFileFormat.xlWorkbookNormal,
        //            null, null, false, false,
        //            COMExcel.XlSaveAsAccessMode.xlExclusive,
        //            false, false, false, false, false);
        //    exApp.Quit();
        //}
        public static DataSet ReadExcelContents(string filePath)
        {
            DataTable dt = null;
            DataSet ds = new DataSet();
            OleDbConnection objConn = null;
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da;
            try
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=TRUE\"";
                objConn = new OleDbConnection(connStr);
                objConn.Open();


                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //if (dt == null)
                //    return null;
                String[] sheetNameArr = new String[dt.Rows.Count];
                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    sheetNameArr[i] = dr["TABLE_NAME"].ToString();
                    i++;
                }
                //MessageBox.Show(sheetNameArr[0]);
                for (int j = 0; j < sheetNameArr.Length; j++)
                {
                    da = new OleDbDataAdapter("select * from [" + sheetNameArr[j] + "]", objConn);
                    da.Fill(ds, "table" + (j + 1).ToString());
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                    dt.Dispose();
            }
            return ds;
        }

        public static void InsertData2Excel(DataRow dr, string filePath)
        {
            DataTable dt = null;
            //DataSet ds = new DataSet();
            OleDbConnection objConn = null;
            OleDbCommand cmd = new OleDbCommand();
            //OleDbDataAdapter da;
            try
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;MAXSCANROWS=15;READONLY=FALSE\"";
                objConn = new OleDbConnection(connStr);
                objConn.Open();
                cmd.Connection = objConn;
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //if (dt == null)
                //    return null;
                string sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                string tempStr = "";
                for (int i = 0; i < dr.ItemArray.Count(); i++)
                {
                    if (dr.ItemArray[i].GetType() == typeof(string))
                        tempStr = tempStr + "'" + dr.ItemArray[i].ToString() + "'";
                    else
                        tempStr = tempStr + dr.ItemArray[i].ToString();
                    if (i != dr.ItemArray.Count() - 1)
                        tempStr = tempStr + ",";
                }
                cmd = new OleDbCommand("INSERT INTO [" + sheetName + "] VALUES(" + tempStr + ")", objConn);
                cmd.ExecuteNonQuery();
                //MessageBox.Show(tempStr);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
            }
        }
        public static void InsertData2Excel(DataTable dt, string filePath)
        {
            DataTable dtt = null;
            //DataSet ds = new DataSet();
            OleDbConnection objConn = null;
            OleDbCommand cmd = new OleDbCommand();
            //OleDbDataAdapter da;
            try
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;MAXSCANROWS=15;READONLY=FALSE\"";
                objConn = new OleDbConnection(connStr);
                objConn.Open();
                cmd.Connection = objConn;
                dtt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //if (dt == null)
                //    return null;
                string sheetName = dtt.Rows[0]["TABLE_NAME"].ToString();
                string tempStr = "";
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < dr.ItemArray.Count(); i++)
                    {
                        if (dr.ItemArray[i].GetType() == typeof(string) || dr.ItemArray[i].GetType() == typeof(DateTime))
                            tempStr = tempStr + "'" + dr.ItemArray[i].ToString() + "'";
                        else
                            tempStr = tempStr + dr.ItemArray[i].ToString();
                        if (i != dr.ItemArray.Count() - 1)
                            tempStr = tempStr + ",";
                    }
                    cmd = new OleDbCommand("INSERT INTO [" + sheetName + "] VALUES(" + tempStr + ")", objConn);
                    cmd.ExecuteNonQuery();
                    //MessageBox.Show(tempStr);
                    tempStr = "";
                }
                //MessageBox.Show(tempStr);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
            }
        }

    }
}
